package com.ujiuye.dao.impl;

import com.ujiuye.dao.StudentDao;
import com.ujiuye.model.Student;
import com.ujiuye.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    QueryRunner qr = DruidUtils.getQueryRunner();
    @Override
    public int saveStudent(Student student) {
        String sql="insert into stu values(0,?,?,?,?,?)";
        int row=0;
        try {
            row=qr.update(sql, student.getSname(), student.getGender(), student.getSbir(), student.getHobby(), student.getPhoto());
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }

        return row;
    }

    @Override
    public long getStuCount(Student student) {
        StringBuilder sb = new StringBuilder("select count(sid) from stu where 1=1 ");
        conditionSql(student, sb);

        String sql = sb.toString();
        System.out.println("sql = " + sql);
        Long lo =null;
        try {
            lo = qr.query(sql,new ScalarHandler<Long>());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return lo;
    }

    private void conditionSql(Student student, StringBuilder sb) {
        String sname = student.getSname();
        if(sname != null && sname.trim() !=""){
            sb.append(" and sname like '%" +sname + "%' " );
        }
        String gender = student.getGender();
        if (gender != null && !gender.equals("-1") && gender.trim() !=""){
            sb.append(" and gender = '"+gender + "' ");
        }
    }

    @Override
    public List<Student> selectStudentPage(int start, int size, Student student){
        StringBuilder sb=new StringBuilder("select * from stu where 1=1 ");
        conditionSql(student, sb);
        sb.append("limit ?,?");
        String sql = sb.toString();
        System.out.println( "sql = " + sql);
        List<Student> stuList = null;
        try {
            stuList = qr.query(sql,new BeanListHandler<>(Student.class),start,size) ;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return stuList;
    }

    @Override
    public int deleteStudentById(int id) {
        String sql  = "delete  from stu where sid = ?";
        try {
            int row = qr.update(sql, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public Student getStudentById(int id) {
        String sql = "select * from stu where sid = ? ";
        Student student = null;
        try {
             student = qr.query(sql, new BeanHandler<>(Student.class), id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return student;
    }

    @Override
    public int updataStudentDo(Student student) {
        String sql = "update stu set sname = ? ,gender = ? ,sbir = ? ,hobby = ? ,photo = ? where sid = ? ";
        int row = 0;
        try {
            row = qr.update(sql, student.getSname(), student.getGender(), student.getSbir(), student.getHobby(), student.getPhoto(), student.getSid());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return row;
    }


}
